<?php

namespace App\Console\Commands;

use Illuminate\Console\Command;
use App\Models\StudentModel;
use App\Models\CompanyModel;
use App\Models\StudentCourseModel;
use App\Models\CountStudentModel;
use App\Models\CountOrderModel;
use App\Models\CountCourseModel;
use App\Models\CountIncomeModel;
use Illuminate\Support\Facades\DB;
use App\Models\StudentSignModel;

class CountData extends Command
{
    /**
     * The name and signature of the console command.
     *
     * @var string
     */
    protected $signature = 'app:count-data {--month=}';

    /**
     * The console command description.
     *
     * @var string
     */
    protected $description = 'Command description';

    private static $year = 0;
    private static $month = 0;
    
    /**
     * Execute the console command.
     */
    public function handle()
    {
        self::setTime($this);
        $companies = CompanyModel::pluck('id');
        foreach ($companies as $id) {
            self::countStudent($id);
            self::countOrder($id);
            self::countIncome($id);
            self::countCourse($id);
        }
    }

    private static function setTime(CountData $my){
        $yearMonth = $my->option('month');
        if ($yearMonth) {
            $yearMonth = explode('-', $yearMonth);
            self::$year = intval($yearMonth[0]);
            self::$month = $yearMonth[1]; 
        } else {
            self::$year = date('Y');
            self::$month = date('m');
        }
    }
    /**
     * 统计学生数量
     * @param int $companyId 公司ID
     */
    private static function countStudent($companyId){
        // 总统计
        $totalStudent = StudentModel::where('company_id', $companyId)->count();
        $totalWhere = [
            'company_id' => $companyId,
            'year' => 0,
            'month' => 0,
            'finish' => 0
        ];
        $countTotal = CountStudentModel::where($totalWhere)->first();
        
        if (!$countTotal) {
            $countTotal = new CountStudentModel();
            $totalWhere['desc'] = '总计';
            $countTotal->fill($totalWhere);
            unset($totalWhere['desc']);
        }
        $countTotal->num = $totalStudent;
        $countTotal->save();

        // 总统计 已结课学生
        $totalFinishedStudent = StudentCourseModel::where('company_id', $companyId)
            ->where('pay_status', 1)
            ->selectRaw('sum(course_num + send_num) as total, sum(learned_num) as learned')
            ->groupBy('student_id')
            ->havingRaw("total <= learned")
            ->count();
        $totalWhere['finish'] = 1;
        $countFinishedTotal = CountStudentModel::where($totalWhere)->first();
        if (!$countFinishedTotal) {
            $countFinishedTotal = new CountStudentModel();
            $totalWhere['desc'] = '已结课';
            $countFinishedTotal->fill($totalWhere);
        }
        $countFinishedTotal->num = $totalFinishedStudent;
        $countFinishedTotal->save();
        
        $year = self::$year;
        $month = self::$month;

        // 年度统计
        $yearStudent = StudentModel::where('company_id', $companyId)
            ->whereYear('created_at', $year)
            ->count();
        $yearCount = CountStudentModel::where(['company_id' => $companyId, 'year' => $year,'month' => 0 ])->first();
        if (!$yearCount) {
            $yearCount = new CountStudentModel();
            $yearCount->fill([
                'company_id' => $companyId,
                'year' => $year,
                'month' => 0,
                'desc' => '年统计'
            ]);
        }
        $yearCount->num = $yearStudent;
        $yearCount->save();
        
        // 月度统计
        $monthStudent = StudentModel::where('company_id', $companyId)
            ->whereMonth('created_at', $month)
            ->whereYear('created_at', $year)
            ->count();

        $monthCount = CountStudentModel::where(['company_id' => $companyId, 'year' => $year, 'month' => intval($month)])->first();
        if (!$monthCount) {
            $monthCount = new CountStudentModel();
            $monthCount->fill([
                'company_id' => $companyId,
                'year' => $year,
                'month' => intval($month),
                'num' => $monthStudent,
                'desc' => '月统计'
            ]);
        }
        $monthCount->num = $monthStudent;
        $monthCount->save();
    }

    /**
     * 统计订单数量
     * @param int $companyId 公司ID
     */
    private static function countOrder($companyId){
        $totalOrder = StudentCourseModel::where('company_id', $companyId)->where('pay_status', 1)->count();
        $countTotal = CountOrderModel::where('company_id', $companyId)
            ->where('year', 0)
            ->where('month', 0)
            ->first();
        if (!$countTotal) {
            $countTotal = new CountOrderModel();
            $countTotal->fill([
                'company_id' => $companyId,
                'year' => 0,
                'month' => 0,
                'desc' => '总计'
            ]);
        }
        $countTotal->num = $totalOrder;
        $countTotal->save();

        $year = self::$year;
        $month = self::$month;

        // 统计当年订单总数
        $yearOrder = StudentCourseModel::where('company_id', $companyId)
            ->where('pay_status', 1)
            ->whereYear('created_at', $year)
            ->count();
        $yearCount = CountOrderModel::where('company_id', $companyId)
            ->where('year', $year)
            ->where('month', 0)
            ->first();
        if (!$yearCount) {
            $yearCount = new CountOrderModel();
            $yearCount->fill([
                'company_id' => $companyId,
                'year' => $year,
                'month' => 0,
                'desc' => '年统计'
            ]);
        }
        $yearCount->num = $yearOrder;
        $yearCount->save();
        
        // 统计当月订单总数
        $monthOrder = StudentCourseModel::where('company_id', $companyId)
            ->where('pay_status', 1)            
            ->whereMonth('created_at', $month)
            ->whereYear('created_at', $year)
            ->count();
        $monthCount = CountOrderModel::where('company_id', $companyId)
            ->where('year', $year)
            ->where('month', intval($month))
            ->first();
        if (!$monthCount) {
            $monthCount = new CountOrderModel();
            $monthCount->fill([
                'company_id' => $companyId,
                'year' => $year,
                'month' => intval($month),
                'desc' => '月统计'
            ]);
        }
        $monthCount->num = $monthOrder;
        $monthCount->save();
    }

    /**
     * 统计收入
     * @param int $companyId 公司ID
     */
    private static function countIncome($companyId){
        $totalIncome = StudentCourseModel::where('company_id', $companyId)
            ->where('pay_status', 1)
            ->sum('pay_price');
        $countTotal = CountIncomeModel::where('company_id', $companyId)
            ->where('year', 0)
            ->where('month', 0)
            ->first();
        if (!$countTotal) {
            $countTotal = new CountIncomeModel();
            $countTotal->fill([
                'company_id' => $companyId,
                'year' => 0,
                'month' => 0,
                'finish' => 0,
                'desc' => '总计'
            ]);
        }
        $countTotal->num = $totalIncome;
        $countTotal->save();

        // 统计已结课收入 总金额 / 总课时 * 已结课课时
        $totalFinishedIncome = StudentCourseModel::where('company_id', $companyId)
            ->where('pay_status', 1)
            ->sum(DB::raw('pay_price / (course_num + send_num) * learned_num'));
        $countFinishedTotal = CountIncomeModel::where('company_id', $companyId)
            ->where('year', 0)
            ->where('month', 0)
            ->where('finish', 1)
            ->first();
        if (!$countFinishedTotal) {
            $countFinishedTotal = new CountIncomeModel();
            $countFinishedTotal->fill([
                'company_id' => $companyId,
                'year' => 0,
                'month' => 0,
                'finish' => 1,
                'desc' => '已结课'
            ]);
        }
        $countFinishedTotal->num = round($totalFinishedIncome);
        $countFinishedTotal->save();

        $year = self::$year;
        $month = self::$month;

        // 统计当年总收入
        $yearIncome = StudentCourseModel::where('company_id', $companyId)
            ->where('pay_status', 1)
            ->whereYear('created_at', $year)
            ->sum('pay_price');
        $yearCount = CountIncomeModel::where('company_id', $companyId)
            ->where('year', $year)
            ->where('month', 0)
            ->first();
        if (!$yearCount) {
            $yearCount = new CountIncomeModel();
            $yearCount->fill([
                'company_id' => $companyId,
                'year' => $year,
                'month' => 0,
                'finish' => 0,
                'desc' => '年统计'
            ]);
        }
        $yearCount->num = $yearIncome;
        $yearCount->save();

        // 统计当月总收入
        $monthIncome = StudentCourseModel::where('company_id', $companyId)
            ->where('pay_status', 1)
            ->whereMonth('created_at', $month)
            ->whereYear('created_at', $year)
            ->sum('pay_price');
        $monthCount = CountIncomeModel::where('company_id', $companyId)
            ->where('year', $year)
            ->where('month', intval($month))
            ->first();
        if (!$monthCount) {
            $monthCount = new CountIncomeModel();
            $monthCount->fill([
                'company_id' => $companyId,
                'year' => $year,
                'month' => intval($month),
                'finish' => 0,
                'desc' => '月统计'
            ]);
        }
        $monthCount->num = $monthIncome;
        $monthCount->save();
    }

    /**
     * 统计销课量
     * @param int $companyId 公司ID
     */
     private static function countCourse($companyId){
        // 1.所有课程销售量
        $totalCourseNum = StudentCourseModel::where('company_id', $companyId)
            ->where('pay_status', 1)
            ->sum(DB::raw('course_num + send_num'));
        $countTotal = CountCourseModel::where('company_id', $companyId)
            ->where('year', 0)
            ->where('month', 0)
            ->where('finish', 0)
            ->first();
        if (!$countTotal) {
            $countTotal = new CountCourseModel();
            $countTotal->fill([
                'company_id' => $companyId,
                'year' => 0,
                'month' => 0,
                'finish' => 0,
                'desc' => '总计'
            ]);
        }
        $countTotal->num = $totalCourseNum;
        $countTotal->save();

        // 2.所有已核销的课程
        $totalFinishNum = StudentCourseModel::where('company_id', $companyId)
            ->where('pay_status', 1)
            ->sum('learned_num');
        $finishCount = CountCourseModel::where('company_id', $companyId)
            ->where('year', 0)
            ->where('month', 0)
            ->where('finish', 1)
            ->first();
        if (!$finishCount) {
            $finishCount = new CountCourseModel();
            $finishCount->fill([
                'company_id' => $companyId,
                'year' => 0,
                'month' => 0,
                'finish' => 1,
                'desc' => '已销总计'
            ]);
        }
        $finishCount->num = $totalFinishNum;
        $finishCount->save();
         
        $year = self::$year;
        $month = self::$month;

        /*先统计月份 再根据月份统计年*/
        // 3.当前月份的销售量
        $monthCourseNum = StudentCourseModel::where('company_id', $companyId)
            ->whereYear('created_at', $year)->whereMonth('created_at', $month)
            ->where('pay_status', 1)->sum(DB::raw('course_num + send_num'));
        $monthCount = CountCourseModel::where('company_id', $companyId)
            ->where('year', $year)
            ->where('month', intval($month))
            ->where('finish', 0)
            ->first();
        if (!$monthCount) {
            $monthCount = new CountCourseModel();
            $monthCount->fill([
                'company_id' => $companyId,
                'year' => $year,
                'month' => intval($month),
                'finish' => 0,
                'desc' => '月销售量'
            ]);
        }
        $monthCount->num = $monthCourseNum;
        $monthCount->save();

        // 4.当前月份销课量
        $monthFinishNum = StudentSignModel::where('company_id', $companyId)
            ->whereYear('date', $year)
            ->whereMonth('date', $month)
            ->whereIn('result', [1, 2])
            ->sum('course_num');

        $monthCount = CountCourseModel::where('company_id', $companyId)
            ->where('year', $year)
            ->where('month', intval($month))
            ->where('finish', 1)
            ->first();
        if (!$monthCount) {
            $monthCount = new CountCourseModel();
            $monthCount->fill([
                'company_id' => $companyId,
                'year' => $year,
                'month' => intval($month),
                'finish' => 1,
                'desc' => '月销课'
            ]);
        }
        $monthCount->num = $monthFinishNum;
        $monthCount->save();

        // 统计当前年份的销售量
        $yearCourseNum = CountCourseModel::where('company_id', $companyId)
            ->where('finish', 0)
            ->where('year', $year)
            ->where('month', '>', 0)
            ->sum('num');
        $yearCount = CountCourseModel::where('company_id', $companyId)
            ->where('finish', 0)
            ->where('year', $year)
            ->where('month', 0)
            ->first();
        if (!$yearCount) {
            $yearCount = new CountCourseModel();
            $yearCount->fill([
                'company_id' => $companyId,
                'year' => $year,
                'month' => 0,
                'finish' => 0,
                'desc' => '统计当前年份的课程销售量'
            ]);
        }
        $yearCount->num = $yearCourseNum;
        $yearCount->save();
        // 统计当前年份销课量
        $yearFinishNum = CountCourseModel::where('company_id', $companyId)
            ->where('finish', 1)
            ->where('year', $year)
            ->where('month', '>', 0)
            ->sum('num');
        $yearCount = CountCourseModel::where('company_id', $companyId)
            ->where('finish', 1)
            ->where('year', $year)
            ->where('month', 0)
            ->first();
        if (!$yearCount) {
            $yearCount = new CountCourseModel();
            $yearCount->fill([
                'company_id' => $companyId,
                'year' => $year,
                'month' => 0,
                'finish' => 1,
                'desc' => '统计当前年份的销课量'
            ]);
        }
        $yearCount->num = $yearFinishNum;
        $yearCount->save();
    }
}
